"""Handles the MYSQL DB interface and provides some useful SNOMED-related
functions"""

import MySQLdb
from sets import Set
from sys import stdout

def sql_connect():
    try:
        ## You may have to change the username and password.
        ##
        connection = MySQLdb.connect(host="localhost",
                                     user="root", passwd="root",port=3306,
                                     db="" )
        return connection

    except MySQLdb.OperationalError, message:
        errorMessage = "Error %d:\n%s" % (message[ 0 ], message[ 1 ] )
        print errorMessage

connection = sql_connect()
#connection.autocommit(True)

# ROOT_ID = 138875005

# def create_new_table(tableName, fields):
# 	if check_table_exists(tableName):
# 		return False
# 	sql = "CREATE TABLE `" + tableName + "` ("
# 	
# 	for f in fields:
# 		sql += "`" + f[0] + "` " + f[1] + " NOT NULL, "
# 	
# 	sql = sql[:-2] + ") ENGINE = MYISAM ;"
# 	
# 	cursor = connection.cursor()
# 	cursor.execute(sql)
# 	cursor.close()
# 	return True
# 
# def get_description_concept(descriptionIDs):
# 	sql = "SELECT `CONCEPTID` FROM sct_descriptions_20070731 WHERE `DESCRIPTIONID` IN " + ', '.join([str(id) for id in descriptionIDs])
# 	cursor = connection.cursor()
# 	cursor.execute(sql)
# 	
# 	for id in cursor.fetchall():
# 		print id
# 	
# 	
# 
# def get_active_status(conceptID):
# 	sql = "SELECT  `CONCEPTSTATUS` FROM  `sct_concepts_20070731` WHERE  `CONCEPTID` = " + conceptID
# 	
# 
# def create_new_row(tableName, rowName, rowType):
# 	sql = "ALTER TABLE `" + tableName + "` ADD `" + rowName + "` " + rowType + " NOT NULL;"
# 	
# 	cursor = connection.cursor()
# 	cursor.execute(sql)
# 	cursor.close()
# 	
# def check_element(tableName, field, value):
# 	sql = "SELECT COUNT( * ) FROM  `" + tableName + "` WHERE  `" + field + "` =  '" + value + "'"
# 
# 	cursor = connection.cursor()
# 	cursor.execute(sql)
# 	count = cursor.fetchone()[0]
# 
# 	return count
# 
# def get_lexical_elements(tableName):
# 	sql = "SELECT * FROM `" + tableName + "`;"
# 	
# 	cursor = connection.cursor()
# 	cursor.execute(sql)
# 	
# 	list = []
# 	
# 	for word in cursor.fetchall():
# 		list.append(word[0])
# 	
# 	return list
# 
# def check_table_exists(tableName):
# 	sql = "SHOW TABLES;"
# 	
# 	cursor = connection.cursor()
# 	cursor.execute(sql)
# 	
# 	for name in cursor.fetchall():
# 		if name[0] == tableName:
# 			return True
# 	return False
# 
# def add_to_table(tableName, rowNames, rows):
# 	cursor = connection.cursor()
# 
# 	entries = len(rows)
# 	stdout.write("Adding to " + tableName + " Database: 000%")
# 	stdout.flush()
# 	percentage = 0
# 	lineNum = 0
# 	for i in xrange(len(rows)):
# 		row = rows[i]
# 		for i in xrange(len(row)):
# 			row[i] = row[i].replace("\'", "\\'")
# 			
# 		sql = "INSERT INTO `" + tableName + "` ("
# 		for r in rowNames:
# 			sql += "`" + r[0] + "`, "
# 		sql = sql[:-2] + ") VALUES ( "
# 		for r in row:
# 			sql += "'" + r + "', "
# 		sql = sql[:-2] + ");"
# 		
# 		cursor.execute(sql)	
# 
# 		lineNum += 1
# 		newPercentage = int(lineNum/float(entries) * 100)
# 		if newPercentage > percentage:
# 			percentage = newPercentage
# 			stdout.write("\x08\x08\x08\x08")
# 			stdout.flush()
# 			if percentage < 10:
# 				stdout.write("00" + str(percentage) + "%")
# 			elif percentage < 100:
# 				stdout.write("0" + str(percentage) + "%")
# 			else:
# 				stdout.write(str(percentage) + "%\n")
# 			stdout.flush()
# 	cursor.close()
# 
# def add_to_stop_words(word):
# 	count = check_element("Stop Words", "word", word)
# 	
# 	if count == 0:		
# 		cursor = connection.cursor()
# 		sql = "INSERT INTO `Stop Words` (`word`) VALUES ( '" + word + "');"
# 		cursor.execute(sql)
# 		cursor.close()
# 
# def add_to_negation_words(word):
# 	count = check_element("Negation Words", "word", word)
# 	
# 	if count == 0:		
# 		cursor = connection.cursor()
# 		sql = "INSERT INTO `Negation Words` (`word`) VALUES ( '" + word + "');"
# 		cursor.execute(sql)
# 		cursor.close()	
# 
# def add_to_attribute_words(word):
# 	count = check_element("Attribute Words", "word", word)
# 	
# 	if count == 0:		
# 		cursor = connection.cursor()
# 		sql = "INSERT INTO `Attribute Words` (`word`) VALUES ( '" + word + "');"
# 		cursor.execute(sql)
# 		cursor.close()
# 
# def read_all(tableName):
# 	cursor = connection.cursor()
# 	
# 	sql = "SELECT `CONCEPTID` ,  `FULLYSPECIFIEDNAME` FROM `" + tableName + "`" 
# 	cursor.execute(sql)
# 	
# 	list = {}
# 	
# 	for id, fsn in cursor.fetchall():
# 		list[id] = fsn
# 	cursor.close()
# 	
# 	return list
# 
# def id_ancestors(id):
# 	cursor = connection.cursor()
# 	sql = """select CONCEPT_ID_2, FSN
# 		from ISA_RELATIONSHIPS, CONCEPTS
# 		where CONCEPT_ID = CONCEPT_ID_2
# 		and CONCEPT_ID_1 = %d"""
# 	while id != ROOT_ID:
# 		if not cursor.execute(sql % id):
# 			break
# 		id, nm = cursor.fetchone()
# 		yield id, nm
# 	cursor.close()
# 
# def active_status(st):
# 	if st in [0, 6, 11]:
# 		return True
# 	return False
# 
# def desc_concepts(dids, rem_inactive=True):
# 	sql = """select D.CONCEPT_ID, C.CONCEPT_STATUS
# 		from DESCRIPTIONS as D, CONCEPTS as C
# 		where D.CONCEPT_ID = C.CONCEPT_ID 
# 		and D.DESCRIPTION_ID IN (%s)""" % ', '.join([str(id) for id in dids])
# 	cursor = connection.cursor()
# 	cursor.execute(sql)
# 	res = Set()
# 	for id, st in cursor.fetchall():  # should remove fetchall()
# 		if rem_inactive and not active_status(st):
# 			continue
# 		res.add(id)
# 		cursor.close()
# 	return res
# 
# def concept_fsns(ids):
# 	if not ids:
# 		return {}
# 	sql = "select CONCEPT_ID, FSN from sct_concepts_20070731 where conceptid in (%s)" % (', '.join([str(id) for id in ids]))
# 	res = {}
# 	cursor = connection.cursor()
# 	cursor.execute(sql)
# 	for cid, fsn in cursor.fetchall():   # should remove fetchall()
# 		res[cid] = fsn
# 	cursor.close()
# 	return res
# 
# def conceptFSN(conceptId):
# 	sql = "select fsn from sct_concepts_20070731 where CONCEPTID = (%s)" % (conceptId)
# 	cursor = connection.cursor()
# 	cursor.execute(sql)
# 	fsn = cursor.fetchone()[0]
# 	return fsn
# 
def generalise(conceptID):
	sql = "SELECT  `CONCEPTID2` FROM  `sct_relationships_20070731` WHERE  `RELATIONSHIPTYPE` = 116680003 AND  `CONCEPTID1` = " + str(conceptID)
	cursor = connection.cursor()
	cursor.execute(sql)
	data = cursor.fetchall()
	ret = ""
	for d in data:
		ret += str(int(d[0])) + "|"
	return ret

def specialise(conceptID):
	sql = "SELECT  `CONCEPTID1` FROM  `sct_relationships_20070731` WHERE  `RELATIONSHIPTYPE` = 116680003 AND  `CONCEPTID2` = " + str(conceptID)
	cursor = connection.cursor()
	cursor.execute(sql)
	data = cursor.fetchall()
	
	ret = ""
	for d in data:
		ret += str(int(d[0])) + "|"
 	return ret[:-1]

def leaves(conceptID):
	cList = specialise(conceptID)

	if len(cList) == 0:
		return str(conceptID) + "|"

	children = cList.split("|")
	string = ""
	for child in children:
		string += child + "|"
		string += leaves(child)
	
	return string
# 	
# def locality(conceptID):
# 	sql = "SELECT C1.CONCEPTID AS 'RelationshipID', C2.CONCEPTID AS 'TargetID' FROM (`sct_relationships_20070731` JOIN `sct_concepts_20070731` C1 ON  sct_relationships_20070731.RELATIONSHIPTYPE = C1.CONCEPTID) JOIN `sct_concepts_20070731` C2  ON `sct_relationships_20070731`.CONCEPTID2 = C2.CONCEPTID WHERE  RELATIONSHIPTYPE != 116680003 AND CONCEPTID1 = (%s)" % (conceptID)
# 	cursor = connection.cursor()
# 	cursor.execute(sql)
# 	results = cursor.fetchall()
# 	ret = ""
# 	for relID, cid in results:
# 		ret += str(int(relID)) + "|" + str(int(cid)) + "||"
# 	return ret
# 
# def findRelation(conceptID, relationID):
# 	sql = "SELECT C2.CONCEPTID AS 'TargetID', C2.FSN AS 'TargetFSN' FROM  (`sct_relationships_20070731` JOIN `sct_concepts_20070731` C1 ON  sct_relationships_20070731.RELATIONSHIP_TYPE = C1.CONCEPT_ID) JOIN `sct_concepts_20070731` C2  ON `sct_relationships_20070731`.CONCEPTID2 = C2.CONCEPTID WHERE  RELATIONSHIPTYPE != 116680003 AND CONCEPTID1 = " + str(conceptID) + " AND  RELATIONSHIPTYPE = " + str(relationID)
# 	cursor = connection.cursor()
# 	cursor.execute(sql)
# 	print "FIND RELATION::"
# 	results = cursor.fetchall()
# 	ret = ""
# 	print results
# 	if len(results) == 0:
# 		print "	No matches"
# 		return "NULL"
# 	for cid, fsn in results:
# 		print "	CID (", cid, ") -", fsn	
# 		ret += "(" + str(cid) + ")-" + str(fsn) + ";"
# 	return ret
"""
def get_lex_count(conceptIDList):
	list = []
	print "Getting the lex count for:", conceptIDList
	for conceptID in conceptIDList:
		sql = "SELECT  `Count` ,  `Ratio` FROM  `sct_term_count_20070731` WHERE  `ID` = " + str(conceptID)
	
		cursor = connection.cursor()
		cursor.execute(sql)
		
		count, ratio = cursor.fetchone()
		
		list.append([conceptID, count, ratio])

	return list	

def get_lex_entries(conceptID):
	list = []
	
	print "Getting lexical entry for:", conceptID
	
	sql = "SELECT `IDs` FROM `sct_lexicon_20070731` WHERE `Term` = '" + str(conceptID) + "'"
	cursor = connection.cursor()
	cursor.execute(sql)
	try:
		ids = cursor.fetchone()[0]
		
		for id in ids.split(":"):
			list.append(id)
	
	except:
		pass
		
	return list
"""	
# if __name__ == "__main__":
# 	connection = sql_connect()
# 	conceptID = "125661005"
# 	print "Concept (", conceptID, ") -", conceptFSN(conceptID)
# 	
# 	generalise(conceptID)
# 	specialise(conceptID)
